﻿CREATE PROCEDURE [dbo].[proc_Order_Getlist_Receipt]
	(
		@CompanyId int,
		@StateId int,
		@Stext nvarchar(50),
		@StartIndex int,
		@EndIndex int,
		@StartDate varchar(10),
		@EndDate varchar(10),
		@Search int
	)
AS
Begin

declare @strSql NVARCHAR(4000)
set @strSql=' CompanyId = '+Convert(varchar,@CompanyId)+' and StateId>0 '

if @Search=1
set @strSql=@strSql+' and CusName like ''%'+@Stext+'%'''
else
set @strSql=@strSql+' and Salesman like ''%'+@Stext+'%'''

set @strSql=' 
declare @srq DATETIME,@erq datetime
set @srq=Convert(datetime,'''+@StartDate+' 00:00:00'')
set @erq=Convert(datetime,'''+@EndDate+' 23:59:59'')

	;WITH list As(Select  ROW_NUMBER() OVER (ORDER BY CreateDate DESC)AS Row
		,Id
		,OId
		,IsNull(OrderId,'''') As OrderId
		,CusId
		,CusName
		,CusPerson
		,CusPersonTel
		,CusDescription
		,CusEstimate
		,TypeId
		,TypeName
		,WayId
		,WayName
		,ProcessId
		,ProcessName
		,Salesman
		,SalesmanAccount
		,TradeDate
		,CreateDate
		,YingShouPrice
		,ShiShouPrice
		,Discount
		,PaymentTypeId
		,PaymentType
		,BankId
		,Bank
		,PaymentDescription
		,OtherDescription
		,DepId
		,CompanyId
		,StateId,flag
,NOE_Flag
		,IsNULL((Select Title From Order_State Where Id=[Order].StateId),''...'') As StateName
		,(Select Id From Order_PaymentRecord Where OId=[Order].OId And SWId=1) As PayRecordId
		,rcount as  ReceiptCount
		From [Order] Where 
		'+@strSql+'
		 		and createdate between @srq and @erq
	
	)
	
	Select *,(SELECT COUNT(1) FROM list) AS cid
	--,(select sum(0) from Receipt where companyid='+Convert(varchar,@CompanyId)+' and list.OId=Receipt.Orderid) as ReceiptCount 
	From list  
	Where Row Between '+Convert(varchar,@StartIndex)+' and '+Convert(varchar,@EndIndex)+' Order By Row
	'
	exec(@strSql)
	print @strSql
End
